package com.sduept.nwld.dataserver.manager.statistics;

import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sduept.core.dao.SuperDAO;
import com.sduept.generator.ObjectId;
import com.sduept.nwld.dataserver.model.faulttimeanalysis.BreakerActTimeCount;
import com.sduept.nwld.dataserver.model.faulttimeanalysis.FaultContinuedTimeCount;
import com.sduept.nwld.dataserver.model.faulttimeanalysis.ProtectActTimeCount;


@Service
public class FaultTimeAnalysisCountManager {

	@Autowired
	private SuperDAO dao;
	@Autowired
	private StatisticsCommonManager comM;
	
	/**
	 * 故障持续时间统计
	 */
	// 月统计
	private final static String FAULT_MONTH_COUNT_SQL = "SELECT d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, d.firstFault FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id WHERE f.voltage=? AND f.faultTime BETWEEN ? AND ? AND d.firstFault>0 AND d.firstFault<500000 AND f.faultTime IS NOT NULL"; 
	// 年统计
	private final static String FAULT_YEAR_COUNT_SQL = "SELECT d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, d.firstFault FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id WHERE f.voltage=? AND f.faultTime BETWEEN ? AND ? AND d.firstFault>0 AND d.firstFault<500000 AND f.faultTime IS NOT NULL"; 
	// 将全部数据按照年、月进行统计
	private final static String FAULT_ALL_COUNT_SQL = "SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, d.firstFault FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id WHERE     d.firstFault>0 AND d.firstFault<500000 AND f.faultTime IS NOT NULL GROUP BY d.stationId,date";
	//  将全部数据按照年、月进行统计  加上t_cim_fault的id 以便查找故障详细
	private final static String FAULT_ALL_COUNT_DETAIL_SQL ="SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, d.firstFault  ,f.id as cimFaultId   FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id WHERE d.firstFault>0 AND d.firstFault<500000 AND f.faultTime IS NOT NULL GROUP BY d.stationId,date";;
	
	/**
	 * 保护动作时间统计
	 */
	// 月统计
	private final static String PROTECT_MONTH_COUNT_SQL = "SELECT d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, p.distance_0_moments_time FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id LEFT JOIN t_fault_report_main_protection p ON p.pid=d.id WHERE p.action_type = 'trip' and  f.voltage=? AND f.faultTime BETWEEN ? AND ? AND p.distance_0_moments_time>0 AND p.distance_0_moments_time<500000 AND f.faultTime IS NOT NULL"; 
	// 年统计
	private final static String PROTECT_YEAR_COUNT_SQL = "SELECT d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, p.distance_0_moments_time FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id LEFT JOIN t_fault_report_main_protection p ON p.pid=d.id WHERE p.action_type = 'trip' and  f.voltage=? AND f.faultTime BETWEEN ? AND ? AND p.distance_0_moments_time>0 AND p.distance_0_moments_time<500000 AND f.faultTime IS NOT NULL"; 
	// 将全部数据按照年、月进行统计
	private final static String PROTECT_ALL_COUNT_SQL = "SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, p.distance_0_moments_time FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id LEFT JOIN t_fault_report_main_protection p ON p.pid=d.id WHERE p.action_type = 'trip' and   p.distance_0_moments_time>0 AND p.distance_0_moments_time<500000 AND f.faultTime IS NOT NULL";
//  将全部数据按照年、月进行统计  加上t_cim_fault的id 以便查找故障详细
	private final static String PROTECT_ALL_COUNT_DETAIL_SQL = "SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, p.distance_0_moments_time ,f.id as cimFaultId FROM t_cim_fault f LEFT JOIN t_fault_report_main m ON f.id=m.frid LEFT JOIN t_fault_report_station_detail d ON d.pid=m.id LEFT JOIN t_fault_report_main_protection p ON p.pid=d.id WHERE p.distance_0_moments_time>0 AND p.distance_0_moments_time<500000 AND f.faultTime IS NOT NULL";

	/**
	 * 断路器动作时间统计	
	 */
	// 月统计
	private final static String BREAKER_MONTH_COUNT_SQL = "SELECT b.station_id, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, b.action_time_interval FROM t_breaker_action_info b LEFT JOIN t_cim_fault f ON b.faultId=f.id WHERE f.voltage=? AND f.faultTime BETWEEN ? AND ? AND b.action_time_interval>0 AND f.faultTime IS NOT NULL"; 
	// 年统计
	private final static String BREAKER_YEAR_COUNT_SQL = "SELECT b.station_id, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, b.action_time_interval FROM t_breaker_action_info b LEFT JOIN t_cim_fault f ON b.faultId=f.id WHERE f.voltage=? AND f.faultTime BETWEEN ? AND ? AND b.action_time_interval>0 AND f.faultTime IS NOT NULL"; 
	// 将全部数据按照年、月进行统计
	private final static String BREAKER_ALL_COUNT_SQL = "SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, b.station_id, f.voltage, b.action_time_interval FROM t_breaker_action_info b LEFT JOIN t_cim_fault f ON b.faultId=f.id WHERE    b.action_time_interval>0 AND f.faultTime IS NOT NULL GROUP BY b.station_id,date";
//  将全部数据按照年、月进行统计  加上t_cim_fault的id 以便查找故障详细
	private final static String BREAKER_ALL_COUNT_DETAIL_SQL = "SELECT DATE_FORMAT(f.faultTime,'%Y-%m') AS date, b.station_id, f.voltage, b.action_time_interval ,f.id as cimFaultId  FROM t_breaker_action_info b LEFT JOIN t_cim_fault f ON b.faultId=f.id WHERE b.action_time_interval>0 AND f.faultTime IS NOT NULL GROUP BY b.station_id,date";

	private DecimalFormat df = new DecimalFormat("#0.00");
	
	private int objectTransToInteger(Object obj) {
		String str = obj.toString();
		Double d = Double.parseDouble(str);
		return d.intValue();
	}
	
	/**
	 * 统计故障持续时间
	 * @throws SQLException 
	 */
	public void countFaultContinuedTime(Integer year,Integer month,Integer vol) {
		if(null == year) {
			return;
		}
		List<FaultContinuedTimeCount> counts = new ArrayList<FaultContinuedTimeCount>();
		String sql = (month==null?FAULT_YEAR_COUNT_SQL:FAULT_MONTH_COUNT_SQL);
		List<?> list = comM.getListByCondition(year,month,vol,sql);
		// d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, SUM(d.firstFault)
		Map<String, List<Object>> companyMap = comM.getCompanyMap(list,0);
		for (String company : companyMap.keySet()) {
			List<Object> compList = companyMap.get(company);
			if(null != compList) {
				int totalTime = 0; // 故障持续时间总和(微秒)
				int num = 0; //个数
				for (Object object : compList) {
					Object[] obj = (Object[]) object;
//					Double time = (Double) obj[3];
					int time = objectTransToInteger(obj[3]);
					totalTime += time;
					num++;
				}
				FaultContinuedTimeCount record = new FaultContinuedTimeCount();
				record.setId(ObjectId.get().toString());
				record.setYear(year);
				record.setMonth(month);
				record.setVoltage(vol);
				record.setSubCompany(company);
				record.setStatus(0);
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setFaultTimeAve(Float.parseFloat(faultTimeAve));
				}
				counts.add(record);
			}
		}
		try {
			dao.persistentBatch(FaultContinuedTimeCount.class, counts);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
	/**
	 * 统计故障持续时间
	 * @throws SQLException 
	 */
	public Map<String, List<Object>> countFaultContinuedTimeDetail(Integer year,Integer month,Integer vol) {
		if(null == year) {
			return null;
		}
		List<FaultContinuedTimeCount> counts = new ArrayList<FaultContinuedTimeCount>();
		String sql = (month==null?FAULT_YEAR_COUNT_SQL:FAULT_MONTH_COUNT_SQL);
		List<?> list = comM.getListByCondition(year,month,vol,sql);
		Map<String, List<Object>> companyMap = comM.getCompanyMap(list,0);
		for (String company : companyMap.keySet()) {
			List<Object> compList = companyMap.get(company);
			if(null != compList) {
				int totalTime = 0; // 故障持续时间总和(微秒)
				int num = 0; //个数
				for (Object object : compList) {
					Object[] obj = (Object[]) object;
					int time = objectTransToInteger(obj[3]);
					totalTime += time;
					num++;
				}
				FaultContinuedTimeCount record = new FaultContinuedTimeCount();
				record.setId(ObjectId.get().toString());
				record.setYear(year);
				record.setMonth(month);
				record.setVoltage(vol);
				record.setSubCompany(company);
				record.setStatus(0);
				String faultTimeAve = null;
				if(num!=0) {
					// 将微秒转为毫秒
					 faultTimeAve  = df.format((float)totalTime/(1000*num));
					record.setFaultTimeAve(Float.parseFloat(faultTimeAve));
				}
				counts.add(record);
			}
		}
		try {
			dao.persistentBatch(FaultContinuedTimeCount.class, counts);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return companyMap;
	}
	
	public void countAllFaultContinuedTime() {
		List<FaultContinuedTimeCount> records = new ArrayList<FaultContinuedTimeCount>();
		String sql = FAULT_ALL_COUNT_SQL;
		List<?> list = dao.nativeQuery(sql);
		// DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, SUM(d.firstFault)
		Map<String, List<Object>> map = comM.genMap(list);
		for (String key : map.keySet()) {
			List<Object> lt = map.get(key);
			if(null != lt) {
				int totalTime = 0;
				int num = 0;
				for (Object obj : lt) {
					Object[] objs = (Object[])obj;
					if(null != objs[3]) {
						int faultTime = objectTransToInteger(objs[3]);
						totalTime += faultTime; 
						num++;
					}
				}
				FaultContinuedTimeCount record = new FaultContinuedTimeCount();
				record.setId(ObjectId.get().toString());
				String[] d = ((String)key).split("-");
				record.setYear(Integer.parseInt(d[0]));
				record.setMonth(Integer.parseInt(d[1]));
				int voltage = objectTransToInteger(d[2]);
				record.setVoltage(voltage);
				record.setStatus(0);
				record.setSubCompany(d[3].toString());
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setFaultTimeAve(Float.parseFloat(faultTimeAve));
				}
				records.add(record);
			}
		}
		try {
			dao.persistentBatch(FaultContinuedTimeCount.class, records);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
	public Map<String, List<Object>> countAllFaultContinuedTimeDetail() {
		 Map<String, List<Object>> getMap = new HashMap<String, List<Object>>();
		//List<FaultContinuedTimeCount> records = new ArrayList<FaultContinuedTimeCount>();
		String sql = FAULT_ALL_COUNT_DETAIL_SQL;//FAULT_ALL_COUNT_SQL
		List<?> list = dao.nativeQuery(sql);
		// DATE_FORMAT(f.faultTime,'%Y-%m') AS date, d.stationId, f.voltage, SUM(d.firstFault)
		Map<String, List<Object>> map = comM.genMap(list);
		getMap = getMap(map);
		return getMap;
	}
	
	
	private  Map<String, List<Object>>  getMap(Map<String, List<Object>> map) {
		 Map<String, List<Object>> getMap = new HashMap<String, List<Object>>();
		for(Entry<String,List<Object>> entry:map.entrySet()) {
			String[] key = entry.getKey().split("-");
			String company = key[key.length-1];
			List<Object> lt = getMap.get(company);
			if(null == lt) {
				lt = new ArrayList<Object>();
				getMap.put(company, lt);
			}
			for (Object object : entry.getValue()) {
				lt.add(object);
			}
		}
		return getMap;
	}
	
	
	
	/**
	 * 根据年、月、电压等级，查询故障持续时间
	 * @param year
	 * @param month
	 * @param vol
	 * @return 
	 */
	public List<?> getFaultTimeDetail(Integer year,Integer month,Integer vol) {
		String sql = "select * from fault_continued_time_detail_view where 1=1 ";
		if(null == year) {
			return null;
		} else {
			sql+=" and year = '"+year+"'";
			if(null != month) {
				sql+=" and month = '"+month+"'";
			}
			if(null != vol) {
				sql+=" and voltage = '"+vol+"'";
			}
			sql+=" order by year";
			List<?> list = dao.nativeQuery(sql);
			return list;
		}
	}
	
	/**
	 * 统计保护动作时间
	 * @param year
	 * @param month
	 * @param vol
	 */
	public void countProtectActTimeCount(Integer year,Integer month,Integer vol) {
		if(null == year) {
			return;
		}
		List<ProtectActTimeCount> counts = new ArrayList<ProtectActTimeCount>();
		String sql = (month==null?PROTECT_YEAR_COUNT_SQL:PROTECT_MONTH_COUNT_SQL);
		List<?> list = comM.getListByCondition(year,month,vol,sql);
		// d.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, SUM(p.distance_0_moments_time)
		Map<String, List<Object>> companyMap = comM.getCompanyMap(list,0);
		for (String company : companyMap.keySet()) {
			List<Object> compList = companyMap.get(company);
			if(null != compList) {
				int totalTime = 0; // 故障持续时间总和(微秒)
				int num = 0; //个数
				for (Object object : compList) {
					Object[] obj = (Object[]) object;
//					BigDecimal time = (BigDecimal) obj[3];
					int time = objectTransToInteger(obj[3]);
					totalTime += time;
					num++;
				}
				ProtectActTimeCount record = new ProtectActTimeCount();
				record.setId(ObjectId.get().toString());
				record.setYear(year);
				record.setMonth(month);
				record.setVoltage(vol);
				record.setSubCompany(company);
				record.setStatus(0);
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setActTimeAve(Float.parseFloat(faultTimeAve));
				}
				counts.add(record);
			}
		}
		try {
			dao.persistentBatch(ProtectActTimeCount.class, counts);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void countAllProtectActTimeCount() {
		List<ProtectActTimeCount> records = new ArrayList<ProtectActTimeCount>();
		String sql = PROTECT_ALL_COUNT_SQL;
		List<?> list = dao.nativeQuery(sql);
		Map<String, List<Object>> map = comM.genMap(list);
		for (String key : map.keySet()) {
			List<Object> lt = map.get(key);
			if(null != lt) {
				int totalTime = 0;
				int num = 0;
				for (Object obj : lt) {
					Object[] objs = (Object[])obj;
					if(null != objs[3]) {
						int faultTime = objectTransToInteger(objs[3]);
						totalTime += faultTime; 
						num++;
					}
				}
				ProtectActTimeCount record = new ProtectActTimeCount();
				record.setId(ObjectId.get().toString());
				String[] d = ((String)key).split("-");
				record.setYear(Integer.parseInt(d[0]));
				record.setMonth(Integer.parseInt(d[1]));
				int voltage = objectTransToInteger(d[2]);
				record.setVoltage(voltage);
				record.setStatus(0);
				record.setSubCompany(d[3].toString());
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setActTimeAve(Float.parseFloat(faultTimeAve));
				}
				records.add(record);
			}
		}
		try {
			dao.persistentBatch(ProtectActTimeCount.class, records);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Map<String, List<Object>> countAllProtectActTimeCountDetail() {
		 Map<String, List<Object>> getMap = new HashMap<String, List<Object>>();
		String sql = PROTECT_ALL_COUNT_DETAIL_SQL;// PROTECT_ALL_COUNT_SQL
		List<?> list = dao.nativeQuery(sql);
		Map<String, List<Object>> map = comM.genMap(list);
		getMap = getMap(map);
		return getMap;
	}
	
	
	
	/**
	 * 根据年、月、电压等级，查询保护动作时间
	 * @param year
	 * @param month
	 * @param vol
	 * @return 
	 */
	public List<?> getProtectActTimeDetail(Integer year,Integer month,Integer vol) {
		String sql = "select * from protect_act_time_detail_view where 1=1 ";
		if(null == year) {
			return null;
		} else {
			sql+=" and year = '"+year+"'";
			if(null != month) {
				sql+=" and month = '"+month+"'";
			}
			if(null != vol) {
				sql+=" and voltage = '"+vol+"'";
			}
			sql+=" order by year";
			List<?> list = dao.nativeQuery(sql);
			return list;
		}
	}
	
	/**
	 * 统计断路器动作时间
	 * @param year
	 * @param month
	 * @param vol
	 */
	public void countBreakerActTimeCount(Integer year,Integer month,Integer vol) {
		if(null == year) {
			return;
		}
		List<BreakerActTimeCount> counts = new ArrayList<BreakerActTimeCount>();
		String sql = (month==null?BREAKER_YEAR_COUNT_SQL:BREAKER_MONTH_COUNT_SQL);
		List<?> list = comM.getListByCondition(year,month,vol,sql);
		// b.stationId, DATE_FORMAT(f.faultTime,'%Y-%m') AS date, f.voltage, SUM(b.action_time_interval)
		Map<String, List<Object>> companyMap = comM.getCompanyMap(list,0);
		for (String company : companyMap.keySet()) {
			List<Object> compList = companyMap.get(company);
			if(null != compList) {
				int totalTime = 0; // 断路器时间总和(微秒)
				int num = 0; //个数
				for (Object object : compList) {
					Object[] obj = (Object[]) object;
//					BigDecimal time = (BigDecimal) obj[3];
					int faultTime = objectTransToInteger(obj[3]);
					totalTime += faultTime;
					num++;
				}
				BreakerActTimeCount record = new BreakerActTimeCount();
				record.setId(ObjectId.get().toString());
				record.setYear(year);
				record.setMonth(month);
				record.setVoltage(vol);
				record.setSubCompany(company);
				record.setStatus(0);
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setActionTimeInterval(Float.parseFloat(faultTimeAve));
				}
				counts.add(record);
			}
		}
		try {
			dao.persistentBatch(BreakerActTimeCount.class, counts);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void countAllBreakerActTimeCount() {
		List<BreakerActTimeCount> records = new ArrayList<BreakerActTimeCount>();
		String sql = BREAKER_ALL_COUNT_SQL;
		List<?> list = dao.nativeQuery(sql);
		Map<String, List<Object>> map = comM.genMap(list);
		for (String key : map.keySet()) {
			List<Object> lt = map.get(key);
			if(null != lt) {
				int totalTime = 0;
				int num = 0;
				for (Object obj : lt) {
					Object[] objs = (Object[])obj;
					if(null != objs[3]) {
						int faultTime = objectTransToInteger(objs[3]);
						totalTime += faultTime; 
						num++;
					}
				}
				BreakerActTimeCount record = new BreakerActTimeCount();
				record.setId(ObjectId.get().toString());
				String[] d = ((String)key).split("-");
				record.setYear(Integer.parseInt(d[0]));
				record.setMonth(Integer.parseInt(d[1]));
				int voltage = objectTransToInteger(d[2]);
				record.setVoltage(voltage);
				record.setStatus(0);
				record.setSubCompany(d[3].toString());
				if(num!=0) {
					// 将微秒转为毫秒
					String faultTimeAve = df.format((float)totalTime/(1000*num));
					record.setActionTimeInterval(Float.parseFloat(faultTimeAve));
				}
				records.add(record);
			}
		}
		try {
			dao.persistentBatch(BreakerActTimeCount.class, records);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public Map<String, List<Object>> countAllBreakerActTimeCountDetail() {
		 Map<String, List<Object>> getMap = new HashMap<String, List<Object>>();
		 String sql = BREAKER_ALL_COUNT_DETAIL_SQL; //BREAKER_ALL_COUNT_SQL
			List<?> list = dao.nativeQuery(sql);
			Map<String, List<Object>> map = comM.genMap(list);
		getMap = getMap(map);
		return getMap;
	}
	
	
	
	
	/**
	 * 根据年、月、电压等级，查询断路器动作时间
	 * @param year
	 * @param month
	 * @param vol
	 * @return 
	 */
	public List<?> getBreakerActTimeDetail(Integer year,Integer month,Integer vol) {
		String sql = "select * from breaker_action_time_detail_view where 1=1 ";
		if(null == year) {
			return null;
		} else {
			sql+=" and year = '"+year+"'";
			if(null != month) {
				sql+=" and month = '"+month+"'";
			}
			if(null != vol) {
				sql+=" and voltage = '"+vol+"'";
			}
			sql+=" order by year";
			List<?> list = dao.nativeQuery(sql);
			return list;
		}
	}
	
	
}
